/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */

package db.JDBC;

import core.Course;
import core.Program;
import java.sql.Connection;
import core.User;
import db.interfaces.UserDAO;
import java.util.Collection;

/**
 *
 * @author Anton/Peter
 */
public class UserJdbcDAO extends BaseJdbcDAO implements UserDAO
{
    /**
     *
     * @param con Connection object to database.
     */
    public UserJdbcDAO(Connection con)
    {
        super(con);
    }

    /**
     * 
     * @param id User's id.
     * @return shallow copy of user from database.
     */
    @Override
    public User getUserByUserId(long id)
    {
        return super.getUserByUserId(id);
    }

    /**
     * Shallow copy of user from database.
     * @param email User's email (should/are be unique).
     * @return shallow copy of user from database. (if param email is null, null is returned).
     */
    public User getUserByEmail(String email)
    {
        if(email == null)
            return null;
        String sql = "SELECT * FROM Users WHERE email='" + email + "';";
        return createUser(sqlExecuteQuery(sql));
    }
    public Collection<User> searchForUsersByEmail(String beginsWith)
    {
        return search(beginsWith, "email");
    }
    public Collection<User> searchForUsersByFName(String beginsWith)
    {
        return search(beginsWith, "fname");
    }
    public Collection<User> searchForUsersByLName(String beginsWith)
    {
        return search(beginsWith, "lname");
    }
    public Collection<User> searchForUsersByParameters(String beginsWith, String fieldName, boolean asc)
    {
        String sql = "";
        String sort = "ASC";
        if(!asc)
            sort = "DESC";
        if(fieldName.equals(""))            
            sql = "SELECT * FROM Users ORDER BY " + fieldName + " " + sort +";";
        else
            sql = "SELECT * FROM Users WHERE " +
                "id LIKE '" + beginsWith + "%' OR " +
                "fname LIKE '" + beginsWith + "%' OR " +
                "lname LIKE '" + beginsWith + "%' OR " +
                "email LIKE '" + beginsWith + "%' ORDER BY " +
                fieldName + " " + sort +";";
        return createUserCollection(sqlExecuteQuery(sql));
    }
    private Collection<User> search(String beginsWith, String field)
    {
        String sql = "SELECT * FROM Users WHERE " + field + " LIKE '" + beginsWith + "%' ORDER BY " + field + " ASC;";
        return createUserCollection(sqlExecuteQuery(sql));
    }

    /**
     * Collection of shallow user objects from database.
     * @param fname first name of users.
     * @return Collection of shallow user objects (with first name given) from database,
     * if fname is null, null is returned.
     */
    public Collection<User> getUsersByFName(String fname)
    {
        if(fname == null)
            return null;
        String sql = "SELECT * FROM Users WHERE fname='" + fname + "' ORDER BY lname ASC;";
        return createUserCollection(sqlExecuteQuery(sql));
    }

    /**
     * Collection of shallow user objects from database.
     * @param lname last name of users.
     * @return Collection of shallow user objects (with last name given) from database,
     * if lname is null, null is returned.
     */
    public Collection<User> getUsersByLName(String lname)
    {
        if(lname == null)
            return null;
        String sql = "SELECT * FROM Users WHERE lname='" + lname + "' ORDER BY fname ASC;";
        return createUserCollection(sqlExecuteQuery(sql));
    }

    /**
     * Collection of shallow user objects from database.
     * @return Collection of shallow user objects from database.
     */
    public Collection<User> getAllUsers()
    {
        String sql = "SELECT * FROM Users ORDER BY lname ASC, fname ASC, email ASC;";
        return createUserCollection(sqlExecuteQuery(sql));
    }

    /**
     * Add program to user.
     * @param user 
     * @param program
     * @return true if program is successfully added. returns false if user or program is null.
     */
    public boolean addProgramToUser(User user, Program program)
    {
        if(user == null || program == null)
            return false;
        String sql = "SELECT * FROM UserProgram WHERE user='" + user.getId() + "' AND program='" + program.getId() + "';";
        if(contains(sql))
            return false;
        
        sql = "INSERT INTO UserProgram (user, program) " +
                "VALUES ("
                + "'" + user.getId() + "', "
                + "'" + program.getId() + "');";
        return add(sql);
    }
    /**
     * Add a collection of programs to user.
     * if user is null, no program is (of course) added.
     * if program collection is null method just returns.
     * null program in collection is not added.
     *
     * @param user
     * @param programs Collection of Programs.
     */
    public void addProgramsToUser(User user, Collection<Program> programs)
    {
        if(user == null || programs == null)
            return;
        for(Program p : programs)
            addProgramToUser(user, p);
    }
    /**
     * Delete program from user.
     * @param user
     * @param program
     * @return true if program is successfully added. returns false if user or program is null.
     */
    public boolean deleteProgramFromUser(User user, Program program)
    {
        if(user == null || program == null)
            return false;
        String sql = "DELETE FROM UserProgram WHERE user = '" + user.getId() + "' AND program = '" + program.getId() + "';";
        return delete(sql);
    }
    /**
     * Delete a collection of programs from user.
     * if user is null, no program is (of course) removed.
     * if program collection is null method just returns.
     * null program in collection is not removed.
     *
     * @param user
     * @param programs Collection of Programs.
     */
    public void deleteProgramsFromUser(User user, Collection<Program> programs)
    {
        if(user == null || programs == null)
            return;        
        for(Program p : programs)
            deleteProgramFromUser(user, p);
    }

    /**
     * Add course to user.
     * @param user
     * @param course
     * @return true if course is successfully added. returns false if user or course is null.
     */
    public boolean addCourseToUser(User user, Course course)
    {
        if(user == null || course == null)
            return false;
        String sql = "SELECT * FROM UserCourse WHERE user='" + user.getId() + "' AND course='" + course.getId() + "';";
        if(contains(sql))
            return false;
        sql = "INSERT INTO UserCourse (user, course) " +
                "VALUES ("
                + "'" + user.getId() + "', "
                + "'" + course.getId() + "');";
        return add(sql);
    }
    /**
     * Add a collection of courses to user.
     * if user is null, no course is (of course) added.
     * if course collection is null method just returns.
     * null course in collection is not added.
     *
     * @param user
     * @param courses Collection of Courses.
     */
    public void addCoursesToUser(User user, Collection<Course> courses)
    {
        if(user == null || courses == null)
            return;
        for(Course c : courses)
            addCourseToUser(user, c);
    }
    /**
     * Delete course from user.
     * @param user
     * @param course
     * @return true if course is successfully added. returns false if user or course is null.
     */
    public boolean deleteCourseFromUser(User user, Course course)
    {
        if(user == null || course == null)
            return false;
         String sql = "DELETE FROM UserCourse WHERE user = '" + user.getId() + "' AND course = '" + course.getId() + "';";
        return delete(sql);
    }
    /**
     * Delete a collection of courses from user.
     * if user is null, no course is (of course) removed.
     * if course collection is null method just returns.
     * null course in collection is not removed.
     *
     * @param user
     * @param courses Collection of Courses.
     */
    public void deleteCoursesFromUser(User user, Collection<Course> courses)
    {
        if(user == null || courses == null)
            return;
        for(Course c : courses)
            deleteCourseFromUser(user, c);
    }

    /**
     * 
     * @param email
     * @return true/false if db contains email.
     */
    public boolean containsUser(String email)
    {
        if(email == null)
            return false;
        String sql = "SELECT * FROM Users WHERE email='" + email + "';";
        return contains(sql);
    }
    /**
     * 
     * @param user
     * @return true/false if db contains user(user's id).
     */
    public boolean containsUser(User user)
    {
        if(user == null)
            return false;
        String sql = "SELECT * FROM Users WHERE id='" + user.getId() + "';";
        return contains(sql);
    }
    /**
     * Add user to db (shallow add, not collections).
     * @param user
     * @return true if add is successfully performed. Else false.
     */
    public boolean addUser(User user) 
    {
        if(user == null)
            return false;
        String sql = "INSERT INTO Users (fname, lname, email, password, level) " +
                           "VALUES ("
                           + "'" + user.getFname() + "', "
                           + "'" +  user.getLname() + "', "
                           + "'" +  user.getEmail() + "', "
                           + "'" +  user.getPassword() + "', "
                           + "'" +  user.getLevel() + "');";
        boolean b = add(sql);
        user.setId(getLatestAddedId("Users"));
        return b;
    }
    /**
     * Edit user to db (shallow edit, not collections).
     * @param user
     * @return true if edit is successfully performed. Else false.
     */
    public boolean editUser(User user)
    {
        if(user == null)
            return false;
        String sql = "UPDATE Users SET  " +
                "fname='" + user.getFname() +
                "', lname = '" + user.getLname() +
                "', email = '" + user.getEmail() +
                "', password = '" + user.getPassword() +
                "', level = '" + user.getLevel() +
                "' WHERE id='" + user.getId() + "';";
        return edit(sql);
    }
    /**
     * Delete user from database. (Shallow delete, not collections, but removed from bindings).
     * @param user
     * @return true if delete is successfully performed. Else false.
     */
    public boolean deleteUser(User user)
    {
        if(user == null)
            return false;
        deleteUserFromAllBindings(user);
        String sql = "DELETE FROM Users WHERE id = ' " + user.getId() + "';";
        return delete(sql);
    }
    /**
     * Remove bindings from Programs and courses.
     * @param user
     */
    private void deleteUserFromAllBindings(User user)
    {
        if(user == null)
            return;
        String sql = "DELETE FROM UserProgram WHERE user = ' " + user.getId() + "'";
        delete(sql);
        sql = "DELETE FROM UserCourse WHERE user = ' " + user.getId() + "'";
        delete(sql);
    }
}


